#!/bin/bash
APP=edu2077

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

dws_course_user_rate_nd="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dws_course_user_rate_nd partition(dt='$do_date')
select
   course_id,
   sum(if(dt >= date_add('$do_date', -6), finish_count_1d, 0)) finish_count_7d,
   sum(if(dt >= date_add('$do_date', -6), finish_num_1d, 0))   finish_num_7d,
   sum(finish_count_1d)                                          finish_count_30d,
   sum(finish_num_1d)                                            finish_num_30d

from ${APP}.dws_course_user_rate_1d
where dt>=date_add('$do_date',-29)
group by course_id  ;
"

dws_interaction_course_review_nd="
insert overwrite table ${APP}.dws_interaction_course_review_nd partition(dt='$do_date')
select
    course_id,
    course_name,
    round(avg(if(dt>=date_add('$do_date',-6),avg_score_1d,0)),2),
    sum(if(dt>=date_add('$do_date',-6),user_num_1d,0)),
    round(avg(if(dt>=date_add('$do_date',-6),positive_ratings_1d,0)),2),
    round(avg(avg_score_1d),2),
    sum(user_num_1d),
    round(avg(positive_ratings_1d),2)
from ${APP}.dws_interaction_course_review_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by course_id,course_name;
"
dws_trade_category_order_nd="
insert overwrite table ${APP}.dws_trade_category_order_nd partition(dt='$do_date')
select
    category_id,
    category_name,
    sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_user_num_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)),
    sum(order_count_1d),
    sum(order_user_num_1d),
    sum(order_total_amount_1d)
from ${APP}.dws_trade_category_order_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by category_id,category_name;
"
dws_trade_course_order_nd="
insert overwrite table ${APP}.dws_trade_course_order_nd partition(dt='$do_date')
select
    course_id,
    course_name,
    sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_user_num_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)),
    sum(order_count_1d),
    sum(order_user_num_1d),
    sum(order_total_amount_1d)
from ${APP}.dws_trade_course_order_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by course_id,course_name;
"
dws_trade_payment_nd="
insert overwrite table ${APP}.dws_trade_payment_nd partition(dt='$do_date')
select
    sum(if(dt>=date_add('$do_date',-6),payment_num_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),payment_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),payment_amount_1d,0)),
    sum(payment_num_1d),
    sum(payment_count_1d),
    sum(payment_amount_1d)
from ${APP}.dws_trade_payment_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date';
"
dws_trade_province_order_nd="
insert overwrite table ${APP}.dws_trade_province_order_nd partition(dt='$do_date')
select
    province_id,
    province_name,
    area_code,
    iso_code,
    iso_3166_2,
    sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_user_num_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),coupon_reduce_amount_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)),
    sum(order_count_1d),
    sum(order_user_num_1d),
    sum(order_original_amount_1d),
    sum(coupon_reduce_amount_1d),
    sum(order_total_amount_1d)
from ${APP}.dws_trade_province_order_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by province_id,province_name,area_code,iso_code,iso_3166_2;
"
dws_trade_subject_order_nd="
insert overwrite table ${APP}.dws_trade_subject_order_nd partition(dt='$do_date')
select
    subject_id,
    subject_name,
    sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_user_num_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)),
    sum(order_count_1d),
    sum(order_user_num_1d),
    sum(order_total_amount_1d)
from ${APP}.dws_trade_subject_order_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by subject_id,subject_name;
"
dws_trade_user_cart_add_nd="
insert overwrite table ${APP}.dws_trade_user_cart_add_nd partition(dt='$do_date')
select
    user_id,
    sum(if(dt>=date_add('$do_date',-6),cart_add_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),cart_add_num_1d,0)),
    sum(cart_add_count_1d),
    sum(cart_add_num_1d)
from ${APP}.dws_trade_user_cart_add_1d
where dt>=date_add('$do_date',-29)
  and dt<='$do_date'
group by user_id;
"
dws_trade_user_order_nd="
insert overwrite table ${APP}.dws_trade_user_order_nd partition(dt='$do_date')
select
    user_id,
    sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)),
    sum(order_count_1d),
    sum(order_original_amount_1d),
    sum(order_total_amount_1d)
from ${APP}.dws_trade_user_order_1d
where dt>=date_add('$do_date',-29)
group by user_id;
"
dws_traffic_page_visitor_page_view_nd="
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_nd partition(dt='$do_date')
select
    mid_id,
    brand,
    model,
    operate_system,
    page_id,
    sum(if(dt>=date_add('$do_date',-6),during_time_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),view_count_1d,0)),
    sum(during_time_1d),
    sum(view_count_1d)
from ${APP}.dws_traffic_page_visitor_page_view_1d
where dt>=date_add('$do_date',-29)
group by mid_id,brand,model,operate_system,page_id;
"
dws_traffic_session_page_view_nd="
insert overwrite table ${APP}.dws_traffic_session_page_view_nd partition(dt='$do_date')
select
    session_id,
    mid_id,
    brand,
    model,
    operate_system,
    version_code,
    channel,
    source_id,
    source_site,
    sum(if(dt>=date_add('$do_date',-6),during_time_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),page_count_1d,0)),
    sum(during_time_1d),
    sum(page_count_1d)
from ${APP}.dws_traffic_session_page_view_1d
where dt>=date_add('$do_date',-29)
group by session_id,mid_id,brand,model,operate_system,version_code,channel,source_id,source_site;
"
dws_video_audition_play_nd="
insert overwrite table ${APP}.dws_video_audition_play_nd partition(dt='$do_date')
select
       course_id,
       course_name,
       subject_id,
       subject_name,
       category_id,
       category_name,
       sum(user_count_1d) user_count_7d,
       sum(audition_user_sum_1d) audition_user_sum_7d
from ${APP}.dws_video_audition_play_1d
where dt>date_add('$do_date',-6)
and dt<='$do_date'
group by course_id, course_name, subject_id, subject_name, category_id, category_name;
"
dws_video_user_rate_nd="
insert overwrite table ${APP}.dws_video_user_rate_nd partition(dt='$do_date')
select
    user_id,
    course_id,
    chapter_id,
    t1.video_id,
    ps
from
(
    select
common.uid  user_id,
sum(appvideo.play_sec )   ps,
appvideo.video_id video_id,
max(from_unixtime(cast((ts/1000)as bigint),"yyyy-MM-dd HH:mm:ss")) creat_time
    from ${APP}.ods_log_inc
  where dt>=date_add('$do_date',-29)
and appvideo.play_sec is not null and appvideo.video_id is not null
    group by common.uid,appvideo.video_id
)t1
left join
(
    select
        video_id,
        chapter_num    chapter_id,
        id  course_id
    from ${APP}.dim_course_full
    where dt>=date_add('$do_date',-29)
)t2
on t1.video_id=t2.video_id;
"

case $1 in
    "dws_course_user_rate_nd" )
        hive -e "$dws_course_user_rate_nd"
    ;;
    "dws_interaction_course_review_nd" )
        hive -e "$dws_interaction_course_review_nd"
    ;;
	 "dws_trade_category_order_nd" )
        hive -e "$dws_trade_category_order_nd"
    ;;
	 "dws_trade_course_order_nd" )
        hive -e "$dws_trade_course_order_nd"
    ;;
	 "dws_trade_payment_nd" )
        hive -e "$dws_trade_payment_nd"
    ;;
	 "dws_trade_province_order_nd" )
        hive -e "$dws_trade_province_order_nd"
    ;;
	 "dws_trade_subject_order_nd" )
        hive -e "$dws_trade_subject_order_nd"
    ;;
	 "dws_trade_user_cart_add_nd" )
        hive -e "$dws_trade_user_cart_add_nd"
    ;;
	 "dws_trade_user_order_nd" )
        hive -e "$dws_trade_user_order_nd"
    ;;
	 "dws_traffic_page_visitor_page_view_nd" )
        hive -e "$dws_traffic_page_visitor_page_view_nd"
    ;;
	 "dws_traffic_session_page_view_nd" )
        hive -e "$dws_traffic_session_page_view_nd"
    ;;
	 "dws_video_audition_play_nd" )
        hive -e "$dws_video_audition_play_nd"
    ;;
	 "dws_video_user_rate_nd" )
        hive -e "$dws_video_user_rate_nd"
    ;;
    "all" )
        hive -e "$dws_course_user_rate_nd$dws_interaction_course_review_nd$dws_trade_category_order_nd$dws_trade_course_order_nd$dws_trade_payment_nd$dws_trade_province_order_nd$dws_trade_subject_order_nd$dws_trade_user_cart_add_nd$dws_trade_user_order_nd$dws_traffic_page_visitor_page_view_nd$dws_traffic_session_page_view_nd$dws_video_audition_play_nd$dws_video_user_rate_nd"
    ;;
esac

